lyfz_repos/铂金版源码/DBFile/SQL/2015-08-13[收集]/利亚方舟发布/数据库（2015-08-11）.sql


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_OrdersWageDigital')
BEGIN
	DROP VIEW [dbo].View_OrdersWageDigital
END 
GO
create view View_OrdersWageDigital 
as
select 
tb_ErpOrderDigital.Id
, Ordv_Number
,Ordv_ViceNumber
,Ordv_DigitalNumber
,Ord_Number
,Ord_Type
,Ord_Class
,Ord_PhotographyCategory                     
,Ord_SeriesName                    
,Ord_SeriesPrice  
,(case when Ord_Type = '1' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else (select top 1 Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_Number = Ord_Number) end) as 拍摄名称
,(select Cus_Name from tb_ErpCustomer where Ord_MainContact=Cus_CustomerNumber) as 客户名称
,(select Cus_Telephone from tb_ErpCustomer where Ord_MainContact=Cus_CustomerNumber) as 客户电话
,Ordv_ClothingName  as  礼服师ID
,dbo.fn_CheckUserIDGetUserName(Ordv_ClothingName) as  礼服师
,Ordv_ClothingStatus as  礼服师状态 
,Ordv_ClothingTime as  选衣时间
, Ordv_FilmSelectionName as  选片师ID
, dbo.fn_CheckUserIDGetUserName(Ordv_FilmSelectionName)  as  选片师
, Ordv_FilmSelectionStatus  as  选片状态 
, Ordv_FilmSelectionTime  as  选片时间
, Ordv_EarlyRepairName  as  初修师ID
, dbo.fn_CheckUserIDGetUserName(Ordv_EarlyRepairName)  as  初修师
, Ordv_EarlyRepairStatus as  初修状态 
, Ordv_EarlyRepairTime  as  初修时间
, Ordv_RefinementName  as  精修师ID
, dbo.fn_CheckUserIDGetUserName(Ordv_RefinementName)as  精修师
, Ordv_RefinementStatus as  精修状态 
, Ordv_RefinementTime  as  精修时间
,Ordv_LookDesignName  as  看设计师ID
,dbo.fn_CheckUserIDGetUserName(Ordv_LookDesignName)  as  看设计师
,Ordv_LookDesignStatus as  看设计状态 
,Ordv_LookDesignTime  as  看设计时间
,Ordv_DesignerName as  设计师ID
,dbo.fn_CheckUserIDGetUserName(Ordv_DesignerName)  as  设计师
,Ordv_DesignerStatus  as  设计状态 
, Ordv_DesignerTime  as  设计时间
,(case when (select Count(*) from (select [OPlist_PickupStatus] from [tb_ErpOrderProductList] where [OPlist_ViceNumber]=Ordv_ViceNumber and OPlist_Type = '2' and [OPlist_PickupStatus] = '0') as ta)>0  then '未取' else 'OK' end) AS 取件状态
,(select top 1 OPlist_PickupTime from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = '2' and OPlist_PickupTime is not null order by OPlist_PickupTime) AS 取件日期
from tb_ErpOrderDigital
left join tb_ErpOrder on tb_ErpOrderDigital.Ordv_Number=tb_ErpOrder.Ord_Number 

GO





IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_OrdersWagePhotography')
BEGIN
	DROP VIEW [dbo].View_OrdersWagePhotography
END 
GO
create view View_OrdersWagePhotography 
as
SELECT  tb_ErpOrdersPhotography.ID
,Ordpg_Number
,Ordpg_ViceNumber
,Ord_Type
,Ord_Class
,Ord_PhotographyCategory
,Ord_SeriesName
,Ord_SeriesPrice
,(select Cus_Name from tb_ErpCustomer where Ord_MainContact=Cus_CustomerNumber) as 客户名称
,(select Cus_Telephone from tb_ErpCustomer where Ord_MainContact=Cus_CustomerNumber) as 客户电话     
,Ordpg_Sights as 拍摄名称
--,Ordpg_SightsLevel
--,[dbo].[fn_GetClassCodeToName](Ordpg_SightsLevel,Ordpg_SightsLevel) as Sc_ClassName
,Ordpg_SightsType as 景点类别
,Ordpg_ApparelQuantity as 服装套数
,Ordpg_Photographer as 摄影师ID
,[dbo].[fn_CheckUserIDGetUserName](Ordpg_Photographer) as 摄影师名称
,Ordpg_PhotographyTime as 拍摄时间
,Ordpg_PhotographyStatus as 拍摄状态
--,[dbo].fn_CheckOrderPhotographyStatus(Ordpg_PhotographyStatus)  as OrdPg_PhotographyStatusName
,Ordpg_PhotographyAssistant as 摄影助理ID
,[dbo].[fn_CheckUserIDGetUserName](Ordpg_PhotographyAssistant) as 摄影助理名称
,Ordpg_MakeupArtist as 化妆师ID
,[dbo].[fn_CheckUserIDGetUserName](Ordpg_MakeupArtist) as 化妆师名称
,Ordpg_MakeupAssistant as 化妆助理ID
,[dbo].[fn_CheckUserIDGetUserName](Ordpg_MakeupAssistant) as 化妆助理名称
,Ordpg_BootDivision as 引导师ID
,[dbo].[fn_CheckUserIDGetUserName](Ordpg_BootDivision) as 引导师名称
--,Ordpg_ReservationPhotographyName
--,Ordpg_ReservationPhotographyTime
--,Ordpg_ReservationPhotographyAssistant
--,Ordpg_ReservationMakeupArtist
--,Ordpg_ReservationMakeupAssistant
--,Ordpg_ReservationBootDivision
FROM tb_ErpOrdersPhotography
left join tb_ErpOrder on tb_ErpOrdersPhotography.Ordpg_Number=tb_ErpOrder.Ord_Number 
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_ErpSatisfactionReturningMattersSet')
BEGIN
	DROP VIEW [dbo].View_ErpSatisfactionReturningMattersSet
END 
GO
create view View_ErpSatisfactionReturningMattersSet
as
SELECT tb_ErpSatisfactionReturningMattersSet.ID
      ,Sfrms_SurveySetID
      ,Sfrms_Contents
      ,Sfrms_EnableStatus
      ,Sfrms_CreateTime
      ,Sfrms_CreateName
      ,Sfrms_UpdateTime
      ,Sfrms_UpdateName
	  ,Sfss_Name
	  ,(CASE sfrms_EnableStatus    WHEN '0'     THEN '启用'    ELSE '不启用' END  )  as sfrms_EnableStatusName
  FROM tb_ErpSatisfactionReturningMattersSet
  left join tb_ErpSatisfactionSurveySet on tb_ErpSatisfactionReturningMattersSet.Sfrms_SurveySetID=tb_ErpSatisfactionSurveySet.ID
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_ErpSatisfactionSurvey')
BEGIN
	DROP VIEW [dbo].View_ErpSatisfactionSurvey
END 
GO
create view View_ErpSatisfactionSurvey
as
SELECT tb_ErpSatisfactionSurvey.ID
      ,Sfs_OrderNumber
      ,Sfs_OrderNumberDeputy
      ,Sfs_EmployeeID
	  ,(select [User_Name] from tb_ErpUser where Sfs_EmployeeID=User_EmployeeID) as Sfs_EmployeeName
      ,Sfs_Type
      ,Sfs_SurveySetID
	  ,Sfss_Name as Sfs_SurveySetName
      ,Sfs_ReturningMattersSetID
	  ,Sfrms_Contents
      ,Sfs_RatingSetID
	  ,Sfrs_Name as Sfs_RatingSetName
      ,Sfs_Remark
      ,Sfs_CreateName as Sfs_CreateNameID
	  ,(select [User_Name] from tb_ErpUser where Sfs_CreateName=User_EmployeeID) as Sfs_CreateName
      ,Sfs_CreateTime
      ,Sfs_UpdateTime
      ,Sfs_UpdateName  as Sfs_UpdateNameID
	  ,(select [User_Name] from tb_ErpUser where Sfs_UpdateName=User_EmployeeID) as Sfs_UpdateName
	  ,Ord_CustomerName1
      ,Ord_CustomerSex1
      ,Ord_CustomerTelephone1
	  ,Sfrs_Scores
	  ,(select count(id) from tb_ErpSatisfactionReturningMattersSet where Sfrms_SurveySetID=Sfs_SurveySetID) as SatisfactionReturningMattersSetCount
  FROM tb_ErpSatisfactionSurvey 
left join tb_ErpSatisfactionSurveySet on Sfs_SurveySetID=tb_ErpSatisfactionSurveySet.ID
left join tb_ErpSatisfactionReturningMattersSet on Sfs_ReturningMattersSetID=tb_ErpSatisfactionReturningMattersSet.ID
left join tb_ErpSatisfactionRatingSet on Sfs_RatingSetID=tb_ErpSatisfactionRatingSet.ID
left join View_Customer_PaymentOrdersTwo on Sfs_OrderNumber=Ord_Number
GO

  IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_OtherCustomers')
BEGIN
	DROP VIEW [dbo].View_OtherCustomers
END 
GO
Create View View_OtherCustomers
as

  SELECT distinct 
       Tsorder_CustomerNumber
      , [Tsorder_CustomerName]
      ,[Tsorder_Telephone]
	  ,Cus_NamePinyin
  FROM Vw_TwoSalesOrder

GO



IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_Customer_PaymentOrdersTwo')
BEGIN
	DROP VIEW [dbo].View_Customer_PaymentOrdersTwo
END 
GO
Create view [dbo].[View_Customer_PaymentOrdersTwo]
as
  select ID,
   Ord_Number, 
   Ord_DividedShop,
   Ord_Type, 
   Ord_SeriesName,
   Ord_SeriesPrice,
   Ord_Class,
   Ord_CustomerListID,
   Ord_MainContact,
   (select Cus_Name from tb_ErpCustomer where Ord_MainContact=Cus_CustomerNumber) as Ord_CustomerName1,
   (select Cus_Sex from tb_ErpCustomer where Ord_MainContact=Cus_CustomerNumber) as Ord_CustomerSex1,
   (select Cus_Telephone from tb_ErpCustomer where Ord_MainContact=Cus_CustomerNumber) as Ord_CustomerTelephone1,
   (CASE Ord_Type   
        WHEN '0'     THEN ( select substring(Ord_CustomerListID,charindex(',',Ord_CustomerListID+',')+1,charindex(',',Ord_CustomerListID+',')-1))  
        WHEN '1'     THEN   ( select substring(Ord_CustomerListID,charindex(',',Ord_CustomerListID+',')+1,charindex(',',Ord_CustomerListID+',')-1))
        ELSE '' END  )  as Ord_CustomerListID2 ,
   (select Cus_Name from tb_ErpCustomer where (CASE Ord_Type   
		WHEN '0'     THEN ( select substring(Ord_CustomerListID,charindex(',',Ord_CustomerListID+',')+1,charindex(',',Ord_CustomerListID+',')-1))  
		WHEN '1'     THEN   ( select substring(Ord_CustomerListID,charindex(',',Ord_CustomerListID+',')+1,charindex(',',Ord_CustomerListID+',')-1))
		ELSE '' END )=Cus_CustomerNumber) as Ord_CustomerName2,
   (select Cus_Telephone from tb_ErpCustomer where (CASE Ord_Type   
		WHEN '0'     THEN ( select substring(Ord_CustomerListID,charindex(',',Ord_CustomerListID+',')+1,charindex(',',Ord_CustomerListID+',')-1))  
		WHEN '1'     THEN   ( select substring(Ord_CustomerListID,charindex(',',Ord_CustomerListID+',')+1,charindex(',',Ord_CustomerListID+',')-1))
		ELSE '' END )=Cus_CustomerNumber) as Ord_CustomerTelephone2,
  (select stuff((select ','+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('')),1,1,'')) as Ord_OrdersPerson,
  (select [Cus_NamePinyin] from tb_ErpCustomer where Ord_MainContact=Cus_CustomerNumber) AS name1,
   ((select  [Cus_NamePinyin] from tb_ErpCustomer where (CASE Ord_Type   
		WHEN '0'     THEN ( select substring(Ord_CustomerListID,charindex(',',Ord_CustomerListID+',')+1,charindex(',',Ord_CustomerListID+',')-1))  
		WHEN '1'     THEN   ( select substring(Ord_CustomerListID,charindex(',',Ord_CustomerListID+',')+1,charindex(',',Ord_CustomerListID+',')-1))
		ELSE '' END )=Cus_CustomerNumber)) AS name2,
		(select stuff((select ','+ OrdPe_OrdersPerson from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('')),1,1,'')) as Ord_OrdersPersonID,
		Ord_CreateDatetime
		,(  SELECT count(id) FROM  tb_ErpOrderProductList where  OPlist_OrderNumber=Ord_Number and OPlist_PickupStatus=0) as PickupStatusCount 
   from tb_ErpOrder

GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_ErpPieceCommissionRecords')
BEGIN
	DROP VIEW [dbo].View_ErpPieceCommissionRecords
END 
GO
Create view [dbo].[View_ErpPieceCommissionRecords]
as
SELECT tb_ErpPieceCommissionRecords.ID
      ,Pcr_OrderNumber
      ,Pcr_DigitalDivision
      ,Pcr_Date
      ,Pcr_CompletionContents
      ,Pcr_Quantity
      ,Pcr_CreateTime
      ,Pcr_EntryPeople
      ,Pcr_Type
	  ,(select [User_Name] from tb_ErpUser where Pcr_DigitalDivision=User_EmployeeID) as Pcr_DigitalDivisionName
	  ,[dbo].[fn_GetClassCodeToName](Pcr_CompletionContents,Pcr_CompletionContents) as Pcr_CompletionContentsName
	  ,(select [User_Name] from tb_ErpUser where Pcr_EntryPeople=User_EmployeeID) as Pcr_EntryPeopleName
	  ,Ord_Type
	  ,Ord_Class
	  ,(select Cus_Name from tempTB_AggregationCustomer where Pcr_OrderNumber=GP_OrderNumber) as Cus_Name
	  ,(select Cus_Telephone from tempTB_AggregationCustomer where Pcr_OrderNumber=GP_OrderNumber) as Cus_Telephone
	  ,Ord_SeriesName
	  ,Ord_SeriesPrice
  FROM tb_ErpPieceCommissionRecords
  left join tb_ErpOrder on tb_ErpPieceCommissionRecords.Pcr_OrderNumber=tb_ErpOrder.Ord_Number

GO




IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_OrdersWagePaymentRecords')
BEGIN
	DROP VIEW [dbo].View_OrdersWagePaymentRecords
END 
GO
Create VIEW [dbo].[View_OrdersWagePaymentRecords]
AS
  SELECT  View_ErpPayment.ID
      ,Pay_OrdNumber
      ,Pay_AmountOf
      ,Pay_OpenSingle
      ,Pay_ThePayee
      ,Pay_PaymentMethod
      ,Pay_OrdersLocation
      ,Pay_ReceivableProject
	  ,Pay_CustomNumber
      ,Pay_Remark
      ,convert(varchar(10),Pay_CreateDatetime,120) as Pay_CreateDatetime
      ,Pay_Category
      ,Pay_TwoPinsCategory
      ,Pay_UserName
      ,Pay_ThePayeeName
      ,Pay_PaymentMethodName
      ,Pay_TwoPinsCategoryName
      ,Pay_FinancialAuditdPeople
      ,Pay_FinancialAudit
      ,Pay_FinancialAuditdPeopleName
      ,Pay_ShootingName
      ,Ord_Number
      ,Ord_DividedShop
      ,Ord_Type
      ,Ord_Class
      , Ord_CustomerName1
      ,name1
      ,Ord_CustomerTelephone1
	  ,(select Tsorder_Name from tb_ErpTwoSalesOrder where Pay_OrdNumber= Tsorder_Number) as Tsorder_Name
	  ,(select Cus_Name from View_DressSaleRentalOrder where Pay_OrdNumber=Dsro_Number) as Cus_Name
	  ,(select Tsorder_CustomerName from View_TwoSalesOrder where Pay_OrdNumber= Tsorder_Number) as Tsorder_CustomerName
	  ,Ord_CreateDatetime
	  ,(select OrdPe_Type from tb_ErpOrdersPerson where OrdPe_OrderNumber=Pay_OrdNumber and OrdPe_OrdersPerson=Pay_OpenSingle) as OrdPe_Type
	  ,Ord_SeriesName
  FROM  View_ErpPayment
   left join View_Customer_PaymentOrdersTwo  on View_ErpPayment.Pay_OrdNumber=View_Customer_PaymentOrdersTwo.Ord_Number 
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_ErpSatisfactionSurvey')
BEGIN
	DROP VIEW [dbo].View_ErpSatisfactionSurvey
END 
GO
Create view [dbo].[View_ErpSatisfactionSurvey]
as
SELECT tb_ErpSatisfactionSurvey.ID
      ,Sfs_OrderNumber
      ,Sfs_OrderNumberDeputy
      ,Sfs_EmployeeID
	  ,(select User_Name from tb_ErpUser where Sfs_EmployeeID=User_EmployeeID) as Sfs_EmployeeName
      ,Sfs_Type
      ,Sfs_SurveySetID
	  ,Sfss_Name as Sfs_SurveySetName
      ,Sfs_ReturningMattersSetID
	  ,Sfrms_Contents
      ,Sfs_RatingSetID
	  ,Sfrs_Name as Sfs_RatingSetName
      ,Sfs_Remark
      ,Sfs_CreateName as Sfs_CreateNameID
	  ,(select User_Name from tb_ErpUser where Sfs_CreateName=User_EmployeeID) as Sfs_CreateName
      ,Sfs_CreateTime
      ,Sfs_UpdateTime
      ,Sfs_UpdateName  as Sfs_UpdateNameID
	  ,(select User_Name from tb_ErpUser where Sfs_UpdateName=User_EmployeeID) as Sfs_UpdateName
	  , Ord_CustomerName1
      ,name1
      ,Ord_CustomerTelephone1
	  ,Sfrs_Scores
	  ,(select count(id) from tb_ErpSatisfactionReturningMattersSet where Sfrms_SurveySetID=Sfs_SurveySetID) as SatisfactionReturningMattersSetCount
  FROM tb_ErpSatisfactionSurvey 
left join tb_ErpSatisfactionSurveySet on Sfs_SurveySetID=tb_ErpSatisfactionSurveySet.ID
left join tb_ErpSatisfactionReturningMattersSet on Sfs_ReturningMattersSetID=tb_ErpSatisfactionReturningMattersSet.ID
left join tb_ErpSatisfactionRatingSet on Sfs_RatingSetID=tb_ErpSatisfactionRatingSet.ID
left join View_Customer_PaymentOrdersTwo on Sfs_OrderNumber=Ord_Number
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_OrdersWageDigital')
BEGIN
	DROP VIEW [dbo].View_OrdersWageDigital
END 
GO
Create  view [dbo].[View_OrdersWageDigital] 
as
select 
tb_ErpOrderDigital.Id
, Ordv_Number
,Ordv_ViceNumber
,Ordv_DigitalNumber
,Ord_Number
,Ord_Type
,Ord_Class
,Ord_PhotographyCategory                     
,Ord_SeriesName                    
,Ord_SeriesPrice  
,(case when Ord_Type = '1' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else (select top 1 Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_Number = Ord_Number) end) as 拍摄名称
,(select Cus_Name from tempTB_AggregationCustomer where Ord_Number=GP_OrderNumber) as 客户名称
,(select Cus_Telephone from tempTB_AggregationCustomer where Ord_Number=GP_OrderNumber) as 客户电话
,Ordv_ClothingName  as  礼服师ID
,dbo.fn_CheckUserIDGetUserName(Ordv_ClothingName) as  礼服师
,Ordv_ClothingStatus as  礼服师状态 
,Ordv_ClothingTime as  选衣时间
, Ordv_FilmSelectionName as  选片师ID
, dbo.fn_CheckUserIDGetUserName(Ordv_FilmSelectionName)  as  选片师
, Ordv_FilmSelectionStatus  as  选片状态 
, Ordv_FilmSelectionTime  as  选片时间
, Ordv_EarlyRepairName  as  初修师ID
, dbo.fn_CheckUserIDGetUserName(Ordv_EarlyRepairName)  as  初修师
, Ordv_EarlyRepairStatus as  初修状态 
, Ordv_EarlyRepairTime  as  初修时间
, Ordv_RefinementName  as  精修师ID
, dbo.fn_CheckUserIDGetUserName(Ordv_RefinementName)as  精修师
, Ordv_RefinementStatus as  精修状态 
, Ordv_RefinementTime  as  精修时间
,Ordv_LookDesignName  as  看设计师ID
,dbo.fn_CheckUserIDGetUserName(Ordv_LookDesignName)  as  看设计师
,Ordv_LookDesignStatus as  看设计状态 
,Ordv_LookDesignTime  as  看设计时间
,Ordv_DesignerName as  设计师ID
,dbo.fn_CheckUserIDGetUserName(Ordv_DesignerName)  as  设计师
,Ordv_DesignerStatus  as  设计状态 
, Ordv_DesignerTime  as  设计时间
,(case when (select Count(*) from (select [OPlist_PickupStatus] from [tb_ErpOrderProductList] where [OPlist_ViceNumber]=Ordv_ViceNumber and OPlist_Type = '2' and [OPlist_PickupStatus] = '0') as ta)>0  then '未取' else 'OK' end) AS 取件状态
,(select top 1 OPlist_PickupTime from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = '2' and OPlist_PickupTime is not null order by OPlist_PickupTime) AS 取件日期
,(case Ord_Type
   when 0 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   when 1 then (select count(id) as id from tb_ErpOrdersPhotography where  Ordv_ViceNumber=Ordpg_ViceNumber and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   when 2 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   else '' end) as 未拍个数
from tb_ErpOrderDigital
left join tb_ErpOrder on tb_ErpOrderDigital.Ordv_Number=tb_ErpOrder.Ord_Number 
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_OrdersWagePhotography')
BEGIN
	DROP VIEW [dbo].View_OrdersWagePhotography
END 
GO

Create view [dbo].[View_OrdersWagePhotography] 
as
SELECT  tb_ErpOrdersPhotography.ID
,Ordpg_Number
,Ordpg_ViceNumber
,Ord_Type
,Ord_Class
,Ord_PhotographyCategory
,Ord_SeriesName
,Ord_SeriesPrice
,(select Cus_Name from tempTB_AggregationCustomer where Ord_Number=GP_OrderNumber) as 客户名称
,(select Cus_Telephone from tempTB_AggregationCustomer where Ord_Number=GP_OrderNumber) as 客户电话
,Ordpg_Sights as 拍摄名称
--,Ordpg_SightsLevel
--,[dbo].[fn_GetClassCodeToName](Ordpg_SightsLevel,Ordpg_SightsLevel) as Sc_ClassName
,Ordpg_SightsType as 景点类别
,Ordpg_ApparelQuantity as 服装套数
,Ordpg_Photographer as 摄影师ID
,[dbo].[fn_CheckUserIDGetUserName](Ordpg_Photographer) as 摄影师名称
,Ordpg_PhotographyTime as 拍摄时间
,Ordpg_PhotographyStatus as 拍摄状态
--,[dbo].fn_CheckOrderPhotographyStatus(Ordpg_PhotographyStatus)  as OrdPg_PhotographyStatusName
,Ordpg_PhotographyAssistant as 摄影助理ID
,[dbo].[fn_CheckUserIDGetUserName](Ordpg_PhotographyAssistant) as 摄影助理名称
,Ordpg_MakeupArtist as 化妆师ID
,[dbo].[fn_CheckUserIDGetUserName](Ordpg_MakeupArtist) as 化妆师名称
,Ordpg_MakeupAssistant as 化妆助理ID
,[dbo].[fn_CheckUserIDGetUserName](Ordpg_MakeupAssistant) as 化妆助理名称
,Ordpg_BootDivision as 引导师ID
,[dbo].[fn_CheckUserIDGetUserName](Ordpg_BootDivision) as 引导师名称
--,Ordpg_ReservationPhotographyName
--,Ordpg_ReservationPhotographyTime
--,Ordpg_ReservationPhotographyAssistant
--,Ordpg_ReservationMakeupArtist
--,Ordpg_ReservationMakeupAssistant
--,Ordpg_ReservationBootDivision
FROM tb_ErpOrdersPhotography
left join tb_ErpOrder on tb_ErpOrdersPhotography.Ordpg_Number=tb_ErpOrder.Ord_Number 

GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_Customer_PaymentOrders')
BEGIN
	DROP VIEW [dbo].Vw_Customer_PaymentOrders
END 
GO
Create view [dbo].Vw_Customer_PaymentOrders
as
select 
tb_ErpOrder.ID
,Ord_Number
,Ord_DividedShop
,Ord_Type 
,Ord_PhotographyCategory
,Ord_SeriesName
,Ord_SeriesPrice
,Ord_Class
,GP_OrderNumber
,GP_CustomerGroupID
,Cus_Name
,Cus_Name_py
,Cus_Telephone
,M_Cus_CustomerNumber
,(select stuff((select ','+ OrdPe_OrdersPerson from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('')),1,1,'')) as Ord_OrdersPersonID
,(select stuff((select ','+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('')),1,1,'')) as Ord_OrdersPerson
,Ord_CreateDatetime
,(  SELECT count(id) FROM  tb_ErpOrderProductList where  OPlist_OrderNumber=Ord_Number and OPlist_PickupStatus=0) as PickupStatusCount 
from tb_ErpOrder
left join tempTB_AggregationCustomer on Ord_Number=GP_OrderNumber
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_OrdersWagePaymentRecords')
BEGIN
	DROP VIEW [dbo].Vw_OrdersWagePaymentRecords
END 
GO
Create VIEW [dbo].Vw_OrdersWagePaymentRecords
AS
  SELECT  View_ErpPayment.ID
      ,Pay_OrdNumber
      ,Pay_AmountOf
      ,Pay_OpenSingle
      ,Pay_ThePayee
      ,Pay_PaymentMethod
      ,Pay_OrdersLocation
      ,Pay_ReceivableProject
	  ,Pay_CustomNumber
      ,Pay_Remark
      ,convert(varchar(10),Pay_CreateDatetime,120) as Pay_CreateDatetime
      ,Pay_Category
      ,Pay_TwoPinsCategory
      ,Pay_UserName
      ,Pay_ThePayeeName
      ,Pay_PaymentMethodName
      ,Pay_TwoPinsCategoryName
      ,Pay_FinancialAuditdPeople
      ,Pay_FinancialAudit
      ,Pay_FinancialAuditdPeopleName
      ,Pay_ShootingName
      ,Ord_Number
      ,Ord_DividedShop
      ,Ord_Type
      ,Ord_Class
      ,Cus_Name as Ord_CustomerName1
      ,Cus_Name_py
      ,Cus_Telephone
	  ,(select Tsorder_Name from tb_ErpTwoSalesOrder where Pay_OrdNumber= Tsorder_Number) as Tsorder_Name
	  ,(select Cus_Name from View_DressSaleRentalOrder where Pay_OrdNumber=Dsro_Number) as Cus_Name
	  ,(select Tsorder_CustomerName from View_TwoSalesOrder where Pay_OrdNumber= Tsorder_Number) as Tsorder_CustomerName
	  ,Ord_CreateDatetime
	  ,(select OrdPe_Type from tb_ErpOrdersPerson where OrdPe_OrderNumber=Pay_OrdNumber and OrdPe_OrdersPerson=Pay_OpenSingle) as OrdPe_Type
	  ,Ord_SeriesName
,Ord_PhotographyCategory
  FROM  View_ErpPayment
   left join Vw_Customer_PaymentOrders  on View_ErpPayment.Pay_OrdNumber=Vw_Customer_PaymentOrders.Ord_Number 

GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_ErpPieceCommissionRecords')
BEGIN
	DROP VIEW [dbo].Vw_ErpPieceCommissionRecords
END 
GO
Create view [dbo].Vw_ErpPieceCommissionRecords
as
SELECT tb_ErpPieceCommissionRecords.ID
      ,Pcr_OrderNumber
      ,Pcr_DigitalDivision
      ,Pcr_Date
      ,Pcr_CompletionContents
      ,Pcr_Quantity
      ,Pcr_CreateTime
      ,Pcr_EntryPeople
      ,Pcr_Type
	  ,(select [User_Name] from tb_ErpUser where Pcr_DigitalDivision=User_EmployeeID) as Pcr_DigitalDivisionName
	  ,[dbo].[fn_GetClassCodeToName](Pcr_CompletionContents,Pcr_CompletionContents) as Pcr_CompletionContentsName
	  ,(select [User_Name] from tb_ErpUser where Pcr_EntryPeople=User_EmployeeID) as Pcr_EntryPeopleName
	  ,Ord_Type
	  ,Ord_Class
	  ,(select Cus_Name from tempTB_AggregationCustomer where Pcr_OrderNumber=GP_OrderNumber) as Cus_Name
	  ,(select Cus_Telephone from tempTB_AggregationCustomer where Pcr_OrderNumber=GP_OrderNumber) as Cus_Telephone
	  ,Ord_SeriesName
	  ,Ord_SeriesPrice
	  ,Pcr_Quantity*(select Wcs_Percentage from tb_ErpWageCommissionSet where Pcr_CompletionContents=Wcs_TypeCode) as 总价格
  FROM tb_ErpPieceCommissionRecords
  left join tb_ErpOrder on tb_ErpPieceCommissionRecords.Pcr_OrderNumber=tb_ErpOrder.Ord_Number
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_ErpSatisfactionSurvey')
BEGIN
	DROP VIEW [dbo].Vw_ErpSatisfactionSurvey
END 
GO
Create view [dbo].Vw_ErpSatisfactionSurvey
as
SELECT tb_ErpSatisfactionSurvey.ID
      ,Sfs_OrderNumber
      ,Sfs_OrderNumberDeputy
      ,Sfs_EmployeeID
	  ,(select User_Name from tb_ErpUser where Sfs_EmployeeID=User_EmployeeID) as Sfs_EmployeeName
      ,Sfs_Type
      ,Sfs_SurveySetID
	  ,Sfss_Name as Sfs_SurveySetName
      ,Sfs_ReturningMattersSetID
	  ,Sfrms_Contents
      ,Sfs_RatingSetID
	  ,Sfrs_Name as Sfs_RatingSetName
      ,Sfs_Remark
      ,Sfs_CreateName as Sfs_CreateNameID
	  ,(select User_Name from tb_ErpUser where Sfs_CreateName=User_EmployeeID) as Sfs_CreateName
      ,Sfs_CreateTime
      ,Sfs_UpdateTime
      ,Sfs_UpdateName  as Sfs_UpdateNameID
	  ,(select User_Name from tb_ErpUser where Sfs_UpdateName=User_EmployeeID) as Sfs_UpdateName
	 ,Cus_Name
      ,Cus_Name_py
      ,Cus_Telephone
	  ,Sfrs_Scores
	  ,(select count(id) from tb_ErpSatisfactionReturningMattersSet where Sfrms_SurveySetID=Sfs_SurveySetID) as SatisfactionReturningMattersSetCount
  FROM tb_ErpSatisfactionSurvey 
left join tb_ErpSatisfactionSurveySet on Sfs_SurveySetID=tb_ErpSatisfactionSurveySet.ID
left join tb_ErpSatisfactionReturningMattersSet on Sfs_ReturningMattersSetID=tb_ErpSatisfactionReturningMattersSet.ID
left join tb_ErpSatisfactionRatingSet on Sfs_RatingSetID=tb_ErpSatisfactionRatingSet.ID
left join Vw_Customer_PaymentOrders on Sfs_OrderNumber=Ord_Number
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_OrdersWageDigital')
BEGIN
	DROP VIEW [dbo].Vw_OrdersWageDigital
END 
GO
Create  view [dbo].Vw_OrdersWageDigital 
as
select 
tb_ErpOrderDigital.Id
, Ordv_Number
,Ordv_ViceNumber
,Ordv_DigitalNumber
,Ord_Number
,Ord_Type
,Ord_Class
,Ord_PhotographyCategory                     
,Ord_SeriesName                    
,Ord_SeriesPrice  
,(case when Ord_Type = '1' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else (select top 1 Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_Number = Ord_Number) end) as 拍摄名称
,(select Cus_Name from tempTB_AggregationCustomer where Ord_Number=GP_OrderNumber) as 客户名称
,(select Cus_Telephone from tempTB_AggregationCustomer where Ord_Number=GP_OrderNumber) as 客户电话
,Ordv_ClothingName  as  礼服师ID
,dbo.fn_CheckUserIDGetUserName(Ordv_ClothingName) as  礼服师
,Ordv_ClothingStatus as  礼服师状态 
,Ordv_ClothingTime as  选衣时间
, Ordv_FilmSelectionName as  选片师ID
, dbo.fn_CheckUserIDGetUserName(Ordv_FilmSelectionName)  as  选片师
, Ordv_FilmSelectionStatus  as  选片状态 
, Ordv_FilmSelectionTime  as  选片时间
, Ordv_EarlyRepairName  as  初修师ID
, dbo.fn_CheckUserIDGetUserName(Ordv_EarlyRepairName)  as  初修师
, Ordv_EarlyRepairStatus as  初修状态 
, Ordv_EarlyRepairTime  as  初修时间
, Ordv_RefinementName  as  精修师ID
, dbo.fn_CheckUserIDGetUserName(Ordv_RefinementName)as  精修师
, Ordv_RefinementStatus as  精修状态 
, Ordv_RefinementTime  as  精修时间
,Ordv_LookDesignName  as  看设计师ID
,dbo.fn_CheckUserIDGetUserName(Ordv_LookDesignName)  as  看设计师
,Ordv_LookDesignStatus as  看设计状态 
,Ordv_LookDesignTime  as  看设计时间
,Ordv_DesignerName as  设计师ID
,dbo.fn_CheckUserIDGetUserName(Ordv_DesignerName)  as  设计师
,Ordv_DesignerStatus  as  设计状态 
, Ordv_DesignerTime  as  设计时间
,(case when (select Count(*) from (select [OPlist_PickupStatus] from [tb_ErpOrderProductList] where [OPlist_ViceNumber]=Ordv_ViceNumber and OPlist_Type = '2' and [OPlist_PickupStatus] = '0') as ta)>0  then '未取' else 'OK' end) AS 取件状态
,(select top 1 OPlist_PickupTime from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = '2' and OPlist_PickupTime is not null order by OPlist_PickupTime) AS 取件日期
from tb_ErpOrderDigital
left join tb_ErpOrder on tb_ErpOrderDigital.Ordv_Number=tb_ErpOrder.Ord_Number 

GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_OrdersWagePhotography')
BEGIN
	DROP VIEW [dbo].Vw_OrdersWagePhotography
END 
GO
Create view [dbo].Vw_OrdersWagePhotography 
as
SELECT  tb_ErpOrdersPhotography.ID
,Ordpg_Number
,Ordpg_ViceNumber
,Ord_Type
,Ord_Class
,Ord_PhotographyCategory
,Ord_SeriesName
,Ord_SeriesPrice
,(select Cus_Name from tempTB_AggregationCustomer where Ord_Number=GP_OrderNumber) as 客户名称
,(select Cus_Telephone from tempTB_AggregationCustomer where Ord_Number=GP_OrderNumber) as 客户电话
,Ordpg_Sights as 拍摄名称
--,Ordpg_SightsLevel
--,[dbo].[fn_GetClassCodeToName](Ordpg_SightsLevel,Ordpg_SightsLevel) as Sc_ClassName
,Ordpg_SightsType as 景点类别
,Ordpg_ApparelQuantity as 服装套数
,Ordpg_Photographer as 摄影师ID
,[dbo].[fn_CheckUserIDGetUserName](Ordpg_Photographer) as 摄影师名称
,Ordpg_PhotographyTime as 拍摄时间
,Ordpg_PhotographyStatus as 拍摄状态
--,[dbo].fn_CheckOrderPhotographyStatus(Ordpg_PhotographyStatus)  as OrdPg_PhotographyStatusName
,Ordpg_PhotographyAssistant as 摄影助理ID
,[dbo].[fn_CheckUserIDGetUserName](Ordpg_PhotographyAssistant) as 摄影助理名称
,Ordpg_MakeupArtist as 化妆师ID
,[dbo].[fn_CheckUserIDGetUserName](Ordpg_MakeupArtist) as 化妆师名称
,Ordpg_MakeupAssistant as 化妆助理ID
,[dbo].[fn_CheckUserIDGetUserName](Ordpg_MakeupAssistant) as 化妆助理名称
,Ordpg_BootDivision as 引导师ID
,[dbo].[fn_CheckUserIDGetUserName](Ordpg_BootDivision) as 引导师名称
--,Ordpg_ReservationPhotographyName
--,Ordpg_ReservationPhotographyTime
--,Ordpg_ReservationPhotographyAssistant
--,Ordpg_ReservationMakeupArtist
--,Ordpg_ReservationMakeupAssistant
--,Ordpg_ReservationBootDivision
FROM tb_ErpOrdersPhotography
left join tb_ErpOrder on tb_ErpOrdersPhotography.Ordpg_Number=tb_ErpOrder.Ord_Number 

GO
update tb_ErpSystemCategory set Sc_ClassName='提成点方案' where Sc_ClassCode='BEBBAFACDCCFBJFI'
GO
if not exists
(select * from syscolumns where id=object_id('tb_ErpPayment') and name='Pay_Type') 
begin
alter table tb_ErpPayment add Pay_Type int 
end
GO
if not exists
(select * from syscolumns where id=object_id('tb_ErpTwoSalesOrder') and name='Tsorder_CustomerNumber') 
begin
alter table tb_ErpTwoSalesOrder add Tsorder_CustomerNumber nvarchar(50) 
end
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_TwoSalesOrder')
BEGIN
	DROP VIEW [dbo].Vw_TwoSalesOrder
END 
GO
Create view [dbo].Vw_TwoSalesOrder
as
SELECT  
dbo.tb_ErpTwoSalesOrder.ID,
Tsorder_Number,
Tsorder_Name, 
(select Cus_Name from tb_ErpCustomer where Tsorder_CustomerNumber=Cus_CustomerNumber) as Tsorder_CustomerName, 
(select Cus_Telephone from tb_ErpCustomer where Tsorder_CustomerNumber=Cus_CustomerNumber) as Tsorder_Telephone, 
(select Cus_NamePinyin from tb_ErpCustomer where Tsorder_CustomerNumber=Cus_CustomerNumber) as Cus_NamePinyin, 
Tsorder_Money, 
Tsorder_Quantity, 
Tsorder_Category, 
Tsorder_OpenSingle, 
Tsorder_PersonHandling, 
Tsorder_Remark, 
Tsorder_CreateDatetime,
Tsorder_UpdateDatetime, 
dbo.fn_CheckUserIDGetUserName(Tsorder_OpenSingle) AS [User_Name],
dbo.fn_CheckUserIDGetUserName(Tsorder_PersonHandling) AS Tsorder_PersonHandlingName,
dbo.fn_GetClassCodeToName(Tsorder_Category,Tsorder_Category) AS Tsorder_CategoryName,
Tsorder_CustomerNumber
FROM      dbo.tb_ErpTwoSalesOrder
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_OrdersWagePaymentRecords')
BEGIN
DROP VIEW [dbo].Vw_OrdersWagePaymentRecords
END 
GO
Create view [dbo].Vw_OrdersWagePaymentRecords
as
SELECT   
tb_ErpPayment.ID, 
Pay_OrdNumber,
Pay_AmountOf,
Pay_OpenSingle, 
Pay_ThePayee,
Pay_PaymentMethod,
Pay_OrdersLocation,
Pay_ReceivableProject,
Pay_Remark, 
convert(varchar(10),Pay_CreateDatetime,120) as Pay_CreateDatetime,
Pay_Category,
Pay_TwoPinsCategory, 
dbo.fn_CheckUserIDGetUserName(Pay_OpenSingle) AS Pay_UserName, 
dbo.fn_CheckUserIDGetUserName(Pay_ThePayee) AS Pay_ThePayeeName, 
dbo.fn_GetClassCodeToName(Pay_PaymentMethod, Pay_PaymentMethod) AS Pay_PaymentMethodName, 
dbo.fn_GetClassCodeToName(Pay_TwoPinsCategory, Pay_TwoPinsCategory) AS Pay_TwoPinsCategoryName, 
Pay_FinancialAuditdPeople,
Pay_FinancialAudit,
dbo.fn_CheckUserIDGetUserName(Pay_FinancialAuditdPeople)AS Pay_FinancialAuditdPeopleName, 
Pay_ShootingName,
Pay_Type,
Ord_DividedShop,
Ord_Type,
Cus_Name as Ord_CustomerName1,
(select Tsorder_Name from Vw_TwoSalesOrder where Pay_OrdNumber= Tsorder_Number) as Tsorder_Name,
(select Tsorder_CustomerName from Vw_TwoSalesOrder where Pay_OrdNumber= Tsorder_Number) as Tsorder_CustomerName,
(select Cus_Name from View_DressSaleRentalOrder where Pay_OrdNumber=Dsro_Number) as Cus_Name,
Ord_CreateDatetime,
Ord_SeriesName,
Ord_PhotographyCategory
FROM    tb_ErpPayment
left join Vw_Customer_PaymentOrders on Pay_OrdNumber=Ord_Number
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_StaffPerformance_OrdersPerson')
BEGIN
	DROP VIEW [dbo].Vw_StaffPerformance_OrdersPerson
END 
GO
create View Vw_StaffPerformance_OrdersPerson
as
SELECT 
ID
,Pay_OrdNumber as 订单号
,Pay_ShootingName as 拍摄阶段
,Pay_Category as 收款类别
,Pay_TwoPinsCategory  as 二销类别编号
,dbo.fn_GetClassCodeToName(Pay_TwoPinsCategory, Pay_TwoPinsCategory) AS 二销类别名称
,Pay_AmountOf as 收款金额
,Pay_OpenSingle as  接单人编号
, dbo.fn_CheckUserIDGetUserName(Pay_OpenSingle) AS 接单人名称
,Pay_ThePayee as 收款人编号
,dbo.fn_CheckUserIDGetUserName(Pay_ThePayee) AS 收款人名称
,Pay_PaymentMethod as 付款方式编号
,dbo.fn_GetClassCodeToName(Pay_PaymentMethod, Pay_PaymentMethod) AS 付款方式名称
,Pay_OrdersLocation as 接单地点 
,Pay_ReceivableProject as 收款项目
,Pay_FinancialAudit as 审核状态
,Pay_FinancialAuditdPeople as 审核人
,Pay_Remark as 备注
,Pay_CreateDatetime as 收款时间
,Pay_Type as 收款类型 
,(case Pay_Type
       when 0 then (select Cus_Name from tempTB_AggregationCustomer where Pay_OrdNumber=GP_OrderNumber)
	   when 1 then (select Tsorder_CustomerName from Vw_TwoSalesOrder where Pay_OrdNumber=Tsorder_Number)
	   when 2 then (select Cus_Name from View_DressSaleRentalOrder where Pay_OrdNumber=Dsro_Number)
	   else '' end) as '客户名称'
,(case Pay_Type
		when 0 then (select Ord_PhotographyCategory from tb_ErpOrder where Pay_OrdNumber=Ord_Number)
		else '' end) as '套系类别'
,(case Pay_Type
		when 0 then (select Ord_SeriesName from tb_ErpOrder where Pay_OrdNumber=Ord_Number)
		else '' end) as '套系名称'
,(case Pay_Type
		when 0 then (select Ord_SeriesPrice from tb_ErpOrder where Pay_OrdNumber=Ord_Number)
		when 1 then (select Tsorder_Money from Vw_TwoSalesOrder where Pay_OrdNumber=Tsorder_Number)
	    when 2 then (select Dsro_Amount from View_DressSaleRentalOrder where Pay_OrdNumber=Dsro_Number)
		 end) as '应收金额'
,(case Pay_Type
		when 0 then (dbo.fn_GetClassCodeToName(Pay_TwoPinsCategory, Pay_TwoPinsCategory)) 
		when 1 then (dbo.fn_GetClassCodeToName(Pay_TwoPinsCategory, Pay_TwoPinsCategory)) 
	    when 2 then Pay_ReceivableProject
		 end) as '项目名称'
FROM tb_ErpPayment
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_StaffPerformance_Photograph')
BEGIN
	DROP VIEW [dbo].Vw_StaffPerformance_Photograph
END 
GO
create View Vw_StaffPerformance_Photograph
as
SELECT 
Ordv_Number as 主订单
,Ordv_ViceNumber as 副订单
,(select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) as 订单类型
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ Ordpg_Sights from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 拍摄名称
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select max(Ordpg_PhotographyTime) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number) 
   when 1 then (select max(Ordpg_PhotographyTime) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber) 
   when 2 then (select max(Ordpg_PhotographyTime) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number) 
   else '' end) as 最后拍摄时间
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   when 1 then (select count(id) as id from tb_ErpOrdersPhotography where  Ordv_ViceNumber=Ordpg_ViceNumber and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   when 2 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   else '' end) as 未拍个数
,(case Ordv_FilmSelectionStatus when 1 then 'OK'  else '未选' end) as 选片状态
,Ordv_FilmSelectionTime as 选片时间
,(case when (select Count(*) from (select [OPlist_PickupStatus] from [tb_ErpOrderProductList] where [OPlist_ViceNumber]=Ordv_ViceNumber and OPlist_Type = '2' and [OPlist_PickupStatus] = '0') as ta)>0  then '未取' else 'OK' end) AS 取件状态
,(select top 1 OPlist_PickupTime from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = '2' and OPlist_PickupTime is not null order by OPlist_PickupTime) AS 取件日期
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ Ordpg_Photographer from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ Ordpg_Photographer from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ Ordpg_Photographer from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 主摄影师ID
 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 主摄影师名称
 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ Ordpg_PhotographyAssistant from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ Ordpg_PhotographyAssistant from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ Ordpg_PhotographyAssistant from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 摄影助理ID
 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_PhotographyAssistant) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_PhotographyAssistant) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_PhotographyAssistant) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 摄影助理名称
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ Ordpg_MakeupArtist from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ Ordpg_MakeupArtist from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ Ordpg_MakeupArtist from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 主化妆ID
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupArtist) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupArtist) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupArtist) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 主化妆名称
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ Ordpg_MakeupAssistant from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ Ordpg_MakeupAssistant from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ Ordpg_MakeupAssistant from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 化妆助理ID
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupAssistant) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupAssistant) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupAssistant) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 化妆助理名称
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ Ordpg_BootDivision from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ Ordpg_BootDivision from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ Ordpg_BootDivision from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 引导师ID
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_BootDivision) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_BootDivision) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_BootDivision) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 引导师名称
,Vw_StaffPerformance_OrdersPerson.ID
,订单号
,拍摄阶段
,收款类别
,二销类别编号
,二销类别名称
,收款金额
,接单人编号
,接单人名称
,收款人编号
,收款人名称
,付款方式编号
,付款方式名称
,接单地点
,收款项目
,审核状态
,审核人
,备注
,收款时间
,收款类型
,客户名称
,套系类别
,套系名称
,应收金额
,项目名称
FROM tb_ErpOrderDigital 
left join Vw_StaffPerformance_OrdersPerson on Ordv_Number=订单号
where 订单号 is not null
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_StaffPerformance_MemberCardRechargeRecord')
BEGIN
	DROP VIEW [dbo].Vw_StaffPerformance_MemberCardRechargeRecord
END 
GO
create View Vw_StaffPerformance_MemberCardRechargeRecord
as
SELECT  ID
      ,Mcrr_Number as 会员编号
      ,Mcrr_RechargeNumber as 充值编号
      ,Mcrr_RechargeName as 充值名称
      ,Mcrr_RechargeAmount as 充值金额
      ,Mcrr_DonateAmount as 赠送金额
      ,Mcrr_DonateDiscount as 消费折扣
      ,Mcrr_PaymentMethod as 支付方式编号
	  ,dbo.fn_GetClassCodeToName(Mcrr_PaymentMethod, Mcrr_PaymentMethod) as 支付方式名称
      ,Mcrr_OrderPerson as 接单人编号
	  ,dbo.fn_CheckUserIDGetUserName(Mcrr_OrderPerson) AS 接单人名称
       ,convert(varchar(10),Mcrr_CreateDatetime,120)  as 创建时间
      ,Mcrr_CreateName as 创建人编号
	  ,dbo.fn_CheckUserIDGetUserName(Mcrr_CreateName) AS 创建人名称
	  ,(select Cus_Name from tb_ErpCustomer where Cus_CustomerNumber=(select  Mc_CustomerNumber from  tb_ErpMemberCard where Mcrr_Number=Mc_Number)) as 客户姓名
  FROM tb_ErpMemberCardRechargeRecord
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_ErpMemberCardPayment')
BEGIN
	DROP VIEW [dbo].Vw_ErpMemberCardPayment
END 
GO
create View Vw_ErpMemberCardPayment
as
SELECT  ID
      ,Mcpt_PaymentNumber as 收款编号
      ,Mcpt_Number as 会员卡编号
      ,Mcpt_PaymentType as 收款类型
      ,Mcpt_PaymentAmount as 收款金额
      ,Mcpt_PaymentMethod as 付款方式编号
      ,dbo.fn_GetClassCodeToName(Mcpt_PaymentMethod, Mcpt_PaymentMethod) as 支付方式名称
      ,Mcpt_PackageClass as 服务套系类别编号
	  ,(case Mcpt_PackageClass
	         when '' then (select stuff((select ','+ Mcpcd_ServiceContent from tb_ErpMemberCardPaymentContentDetail where Mcpt_PaymentNumber=Mcpcd_PaymentNumber for xml path('')),1,1,''))
	         else (select MscPs_PackageName from tb_ErpMemberCardServicePackageSet where Mcpt_PackageClass=MscPs_PackageNumber) end) as 服务套系类别名称
      ,Mcpt_PackagePrice as 服务套系价格
      ,Mcpt_Discount as 优惠折扣
      ,Mcpt_OrderPerson  as 接单人编号
	  ,dbo.fn_CheckUserIDGetUserName(Mcpt_OrderPerson) AS 接单人名称
      ,Mcpt_PaymentRemark as 收款备注
      ,Mcpt_FinancialAudit 财务审核状态
      ,Mcpt_FinancialAuditName as 账务审核人
      ,Mcpt_FinancialAuditDateTime as 财务审核时间
      ,Mcpt_FinancialAuditRemark as 账务审核备注
      ,convert(varchar(10),Mcpt_CreateDatetime,120)  as 创建日期
      ,Mcpt_CreateName as 创建人编号
	  ,dbo.fn_CheckUserIDGetUserName(Mcpt_CreateName) AS 创建人名称
	  ,(select Cus_Name from tb_ErpCustomer where Cus_CustomerNumber=(select  Mc_CustomerNumber from  tb_ErpMemberCard where Mcpt_Number=Mc_Number)) as 客户姓名
  FROM tb_ErpMemberCardPayment
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_ReportTime')
BEGIN
	DROP VIEW [dbo].Vw_ReportTime
END 
GO
create View Vw_ReportTime
as
 with t as(
 select distinct convert(varchar(10),isnull(Vw_OrdersWagePaymentRecords.Pay_CreateDatetime,View_ErpOtherIncomeAndExpenses.Oiae_IEDatetime),120) as Pay_CreateDatetimes
 ,Pay_OrdNumber,Pay_CreateDatetime,Oiae_ProjectName,Oiae_IEDatetime
   from Vw_OrdersWagePaymentRecords 
   full join View_ErpOtherIncomeAndExpenses on Vw_OrdersWagePaymentRecords.Pay_CreateDatetime=View_ErpOtherIncomeAndExpenses.Oiae_IEDatetime 
   )
   , t2 as(
   select distinct convert(varchar(10),isnull(t.Pay_CreateDatetimes,Vw_ErpMemberCardPayment.创建日期),120) as Pay_CreateDatetimes from t
   full join Vw_ErpMemberCardPayment on t.Pay_CreateDatetimes=Vw_ErpMemberCardPayment.创建日期
   )
   select distinct convert(varchar(10),isnull(t2.Pay_CreateDatetimes,Vw_StaffPerformance_MemberCardRechargeRecord.创建时间),120) as Pay_CreateDatetimes from t2
   full join Vw_StaffPerformance_MemberCardRechargeRecord on t2.Pay_CreateDatetimes=Vw_StaffPerformance_MemberCardRechargeRecord.创建时间
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_MonthlyReport')
BEGIN
	DROP VIEW [dbo].Vw_MonthlyReport
END 
GO
create View Vw_MonthlyReport
as
select 
Pay_CreateDatetimes
,(select sum(Pay_AmountOf) from tb_ErpPayment where (Pay_Category='全款' or Pay_Category='预约收款')  and Pay_PaymentMethod!='BEBACCAFEGECFBJFD'  and Pay_CreateDatetime>=Pay_CreateDatetimes and Pay_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayOrdersIncome
, (select sum(Pay_AmountOf) from tb_ErpPayment where Pay_Category='预约补款'  and Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and Pay_CreateDatetime>=Pay_CreateDatetimes and Pay_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayReplenishmentIncome
 ,(select sum(Pay_AmountOf) from tb_ErpPayment where Pay_Category='后期收款'  and Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and Pay_CreateDatetime>=Pay_CreateDatetimes and Pay_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayLateStageIncome
, (select sum(Oiae_Money) from tb_ErpOtherIncomeAndExpenses where Oiae_Type='收入' and Oiae_PaymentMethod!='BEBACCAFEGECFBJFD' and Oiae_IEDatetime>=Pay_CreateDatetimes and Oiae_IEDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayOtherIncome
,(select sum(Mcrr_RechargeAmount) from tb_ErpMemberCardRechargeRecord where Mcrr_CreateDatetime>=Pay_CreateDatetimes and Mcrr_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayMemberIncome
,(select sum(Ord_SeriesPrice) from tb_ErpOrder where ord_class='1' and Ord_CreateDatetime>=Pay_CreateDatetimes and Ord_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayEarlyPerformance
,(select sum(Plu_Amount) from tb_ErpPlusPickItems where Plu_CreateTime>=Pay_CreateDatetimes and Plu_CreateTime<=Pay_CreateDatetimes+' 23:59:59.000') as  DayPluslatepickPerformance 
,(select sum(Tsorder_Money) from tb_ErpTwoSalesOrder where Tsorder_CreateDatetime>=Pay_CreateDatetimes and Tsorder_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayOtherPerformance
, (select sum(Oiae_Money) from tb_ErpOtherIncomeAndExpenses where Oiae_Type='支出'   and Oiae_IEDatetime>=Pay_CreateDatetimes and Oiae_IEDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayExpenditure
,(select sum(Mcpt_PaymentAmount) from tb_ErpMemberCardPayment where Mcpt_CreateDatetime>=Pay_CreateDatetimes and Mcpt_CreateDatetime<=Pay_CreateDatetimes+' 23:59:59.000') as DayMemberCardPaymentIncome
 from 
 (
  select Pay_CreateDatetimes from  Vw_ReportTime
 ) as MonthlyReport
group by Pay_CreateDatetimes
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_YearReport')
BEGIN
	DROP VIEW [dbo].Vw_YearReport
END 
GO
create View Vw_YearReport
as
select 
Pay_CreateDatetimes
,(select sum(Pay_AmountOf) from tb_ErpPayment where (Pay_Category='全款' or Pay_Category='预约收款')  and Pay_PaymentMethod!='BEBACCAFEGECFBJFD'  and convert(varchar,Pay_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayOrdersIncome
, (select sum(Pay_AmountOf) from tb_ErpPayment where Pay_Category='预约补款'  and Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and convert(varchar,Pay_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayReplenishmentIncome
,(select sum(Pay_AmountOf) from tb_ErpPayment where Pay_Category='后期收款'  and Pay_PaymentMethod!='BEBACCAFEGECFBJFD' and convert(varchar,Pay_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayLateStageIncome
, (select sum(Oiae_Money) from tb_ErpOtherIncomeAndExpenses where Oiae_Type='收入' and Oiae_PaymentMethod!='BEBACCAFEGECFBJFD' and convert(varchar,Oiae_IEDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayOtherIncome
,(select sum(Mcrr_RechargeAmount) from tb_ErpMemberCardRechargeRecord where convert(varchar,Mcrr_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayMemberIncome
,(select sum(Ord_SeriesPrice) from tb_ErpOrder where ord_class='1' and convert(varchar,Ord_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayEarlyPerformance
,(select sum(Plu_Amount) from tb_ErpPlusPickItems where convert(varchar,Plu_CreateTime,120) like '%'+Pay_CreateDatetimes+'%') as  DayPluslatepickPerformance 
,(select sum(Tsorder_Money) from tb_ErpTwoSalesOrder where convert(varchar,Tsorder_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayOtherPerformance
, (select sum(Oiae_Money) from tb_ErpOtherIncomeAndExpenses where Oiae_Type='支出'   and convert(varchar,Oiae_IEDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayExpenditure
,(select sum(Mcpt_PaymentAmount) from tb_ErpMemberCardPayment where convert(varchar,Mcpt_CreateDatetime,120) like '%'+Pay_CreateDatetimes+'%') as DayMemberCardPaymentIncome
 from 
 (
     select convert(varchar(7),Pay_CreateDatetimes,120)  as Pay_CreateDatetimes from Vw_ReportTime
  ) as YearReport
group by Pay_CreateDatetimes
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_ProcessStatus')
BEGIN
	DROP VIEW [dbo].Vw_ProcessStatus
END 
GO
create View [dbo].[Vw_ProcessStatus]
as
Select 
ID
,Ordv_Number as 主订单号
,Ordv_ViceNumber as 副订单号
,Ordv_DigitalNumber as 拍摄次数
,(select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) as 订单类型
,(select Ord_Class from tb_ErpOrder where Ordv_Number=Ord_Number) as 订单类别 
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 1 then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber)
   else  (select stuff((select ','+ Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,'')) end) as 拍摄名称
,(select Ord_CreateDateTime from tb_ErpOrder where Ordv_Number=Ord_Number) as 预选时间
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   when 1 then (select count(id) as id from tb_ErpOrdersPhotography where  Ordv_ViceNumber=Ordpg_ViceNumber and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   when 2 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   else '' end) as 未拍个数
, (case Ordv_EarlyRepairStatus  when 2 then 'OK' else '未修'end ) as 初修状态
, (case Ordv_FilmSelectionStatus  when 1 then 'OK' else '未选'end ) as 选片状态
, (case Ordv_RefinementStatus  when 2 then 'OK' else '未修'end ) as 精修状态
, (case Ordv_DesignerStatus  when 2 then 'OK' else '未设计'end ) as 设计状态
, (case Ordv_LookDesignStatus  when 1 then 'OK' else '未看'end ) as 看设计状态
,(select count(id) as id from tb_ErpOrderProductList where OPlist_Type=2 and OPlist_ViceNumber=Ordv_ViceNumber and OPlist_SendStatus=0) as 未发出个数
,(select count(id) as id from tb_ErpOrderProductList where OPlist_Type=2 and OPlist_ViceNumber=Ordv_ViceNumber and OPlist_CompletedStatus=0) as 未完成个数
,(case when (select Count(*) from (select [OPlist_PickupStatus] from [tb_ErpOrderProductList] where [OPlist_ViceNumber]=Ordv_ViceNumber and OPlist_Type = '2' and [OPlist_PickupStatus] = '0') as ta)>0  then '未取' else 'OK' end) AS 取件状态
 from tb_ErpOrderDigital
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_ErpCustomersTrackRecord')
BEGIN
	DROP VIEW [dbo].Vw_ErpCustomersTrackRecord
END 
GO
create View Vw_ErpCustomersTrackRecord
as
SELECT id
      ,TR_CustomerGroupID as 客户组
      ,TR_CustomerID as 客户ID
	  ,(select Cus_Name from tb_ErpCustomer where TR_CustomerID=Cus_CustomerNumber) as 客户姓名
      ,TR_TraceWay   as 沟通方式
      ,TR_TraceType as 沟通类型
      ,TR_SpecificMatters
      ,TR_Communicationstatus as 沟通状态
      ,TR_TraceDetailedly
      ,TR_CallRecording as 录音路径文件
      ,TR_Remark as 备注
      ,TR_TraceDateTime as 沟通日期
      ,TR_TraceTimeLength as 沟通时长
      ,TR_TracePersonID as 跟踪人员ID
	  ,(select User_Name from tb_ErpUser where TR_TracePersonID=User_EmployeeID) as 跟踪人员姓名
      ,TR_CreateDateTime as 创建时间
  FROM dbo.tb_ErpCustomersTrackRecord
GO